---------------------------------------------------------------------------------------
--Update missing Permissions for Human's Protocols.
--12-04-2008 jml29  --TrackIt # 9050
--This script can be run periodically since the source of the problem is not found yet.
---------------------------------------------------------------------------------------
declare
    @protocolId int
    ,@admPersonId int
    ,@mapId int
    ,@admPersonType int
    ,@accessRightBitValue int
    ,@contactPerson bit
    ,@isCurrent bit
    ,@roleid int
    ,@addedPPId int
    ,@removedPPId int
    ,@organizationId int
    ,@moduleType char(1)
-----------------------------------------
IF OBJECT_ID('tempdb..#protocolStaffTable') IS NOT NULL 	DROP TABLE #protocolStaffTable
CREATE TABLE #protocolStaffTable (
    protocolId int
    ,admPersonId int
    ,mapId int
    ,admPersonType int
    ,accessRightBitValue int
    ,contactPerson bit
    ,isCurrent bit
    ,roleid int
    ,addedPPId int
    ,removedPPId int
    ,organizationId int
    ,moduleType char(1)
	,updateDate datetime )
-----------------------------------------
--step 1. Select records with FwkResource_DomainUser_Map.AccessRightBitmask null
--		  This indicates that users don't have either Edit or Manage permission on the Protocol.
-----------------------------------------
insert into #protocolStaffTable 
select distinct
    p.id
    ,ap.[Id]
    ,map.[Id]
    ,1
    ,case when map.ProtocolStaffTypeId = 13 then 256 else 128 end
    ,case when fdcMap.FwkResourceId is null then 0 else 1 end
    ,1
    ,map.ProtocolStaffTypeId
    ,null
    ,null
    ,map.OrganizationId
    ,map.ModuleType
	,getdate()
			from ProtocolStaff_Map map
			left join Protocol p
			on map.ProtocolNumber = p.ProtocolNumber
			left join Protocol p2
			on map.ProtocolId = p.[Id]
			left join ProtocolStaffingType type
			on map.ProtocolStaffTypeId = type.[Id]
			inner join AdmPerson ap
			on map.AdmPersonId = ap.[Id]
			left join FwkDomainUser fdu
			on ap.FwkDomainUserId = fdu.[Id]
			left join FwkResource_DomainUser_Map access
			on fdu.[Id] = access.FwkDomainUserId		
			and p.FwkResourceId = access.FwkResourceId
			left join FwkResource_AdmPerson_Contact_Map fdcMap
			on fdcMap.AdmPersonId = ap.[Id] 
			and fdcMap.FwkResourceId = p.FwkResourceId
			left join FwkDomainOrganization fdo
			on map.OrganizationId = fdo.[Id]
			left join AdmExternalPerson aep
			on aep.AdmPersonId = ap.[Id]
			left join ProtocolProcess pp
			on (map.AddedProtocolProcessId = pp.[Id])
		
			where map.IsCurrent = 1
			and p.IsCurrent = 1
			and map.ModuleType='h'
			and p.ResponsibleIRBOrganizationId in (2,3)
			and p.OverallStatusId not in (2,7)
			and IsNull(access.AccessRightBitmask, 0) =0
			and	fdu.username is not null
----------------------------------------
--select * from 	#protocolStaffTable
----------------------------------------
--step 2 - Backup table - to document last update
----------------------------------------
IF OBJECT_ID('ProtocolStaffUpdatesTbl') IS NULL 
	CREATE TABLE ProtocolStaffUpdatesTbl(
		protocolId int
		,admPersonId int
		,mapId int
		,admPersonType int
		,accessRightBitValue int
		,contactPerson bit
		,isCurrent bit
		,roleid int
		,addedPPId int
		,removedPPId int
		,organizationId int
		,moduleType char(1)
		,updateDate datetime )

----------------------------------------
--select * from ProtocolStaffUpdatesTbl
--drop table ProtocolStaffUpdatesTbl
----------------------------------------

--step 3. - scroll among selected records and update user permissions.
---------------------------------------------------------------------
DECLARE updcursor CURSOR FOR
	  SELECT
		protocolId
		,admPersonId
		,mapId
		,admPersonType
		,accessRightBitValue
		,contactPerson
		,isCurrent
		,roleid
		,addedPPId
		,removedPPId
		,organizationId
		,moduleType
		FROM #protocolStaffTable

	  OPEN updcursor
	  FETCH NEXT FROM updcursor
		INTO
			@protocolId
			,@admPersonId
			,@mapId
			,@admPersonType
			,@accessRightBitValue
			,@contactPerson
			,@isCurrent
			,@roleid
			,@addedPPId
			,@removedPPId
			,@organizationId
			,@moduleType

	  WHILE (@@FETCH_STATUS = 0)
	  BEGIN

		EXEC [dbo].[UpdateStaffPerson]
			@protocolId
			,@admPersonId
			,@mapId
			,@admPersonType
			,@accessRightBitValue
			,@contactPerson
			,@isCurrent
			,@roleid
			,@addedPPId
			,@removedPPId
			,@organizationId
			,@moduleType

	INSERT INTO ProtocolStaffUpdatesTbl
		VALUES (
			@protocolId
			,@admPersonId
			,@mapId
			,@admPersonType
			,@accessRightBitValue
			,@contactPerson
			,@isCurrent
			,@roleid
			,@addedPPId
			,@removedPPId
			,@organizationId
			,@moduleType
			,getdate()	)

	  FETCH NEXT FROM updcursor
		INTO
			@protocolId
			,@admPersonId
			,@mapId
			,@admPersonType
			,@accessRightBitValue
			,@contactPerson
			,@isCurrent
			,@roleid
			,@addedPPId
			,@removedPPId
			,@organizationId
			,@moduleType

	  END --while

	  CLOSE updcursor
	  DEALLOCATE updcursor
------------------------------
IF OBJECT_ID('tempdb..#protocolStaffTable') IS NOT NULL 	DROP TABLE #protocolStaffTable
